Table Definition¶

To create a table, use the CREATE TABLE statement.

CREATE {TABLE | CLASS} [IF NOT EXISTS] table_name
[<subclass_definition>]
[(<column_definition>, ... [, <table_constraint>, ...])]
[AUTO_INCREMENT = initial_value]
[CLASS ATTRIBUTE (<column_definition>, ...)]
[INHERIT <resolution>, ...]
[<table_options>]

    <subclass_definition> ::= {UNDER | AS SUBCLASS OF} table_name, ...

    <column_definition> ::=
        column_name <data_type> [{<default_or_shared_or_ai> | <column_constraint>}] [COMMENT 'column_comment_string']

        <data_type> ::= <column_type> [<charset_modifier_clause>] [<collation_modifier_clause>]

            <charset_modifier_clause> ::= {CHARACTER_SET|CHARSET} {<char_string_literal>|<identifier>}

            <collation_modifier_clause> ::= COLLATE {<char_string_literal>|<identifier>}

        <default_or_shared_or_ai> ::=
            SHARED <value_specification> |
            DEFAULT <value_specification>  |
            AUTO_INCREMENT [(seed, increment)]

        <column_constraint> ::= [CONSTRAINT constraint_name] { NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY <referential_definition> }

            <referential_definition> ::=
                REFERENCES [referenced_table_name] (column_name, ...) [<referential_triggered_action> ...]

                <referential_triggered_action> ::=
                    ON UPDATE <referential_action> |
                    ON DELETE <referential_action>

                    <referential_action> ::= CASCADE | RESTRICT | NO ACTION | SET NULL

    <table_constraint> ::=
        [CONSTRAINT [constraint_name]]
        {
            UNIQUE [KEY|INDEX](column_name, ...) |
            {KEY|INDEX} [constraint_name](column_name, ...) |
            PRIMARY KEY (column_name, ...) |
            <referential_constraint>
        } COMMENT 'index_comment_string'

        <referential_constraint> ::= FOREIGN KEY [<foreign_key_name>](column_name, ...) <referential_definition>

            <referential_definition> ::=
                REFERENCES [referenced_table_name] (column_name, ...) [<referential_triggered_action> ...]

                <referential_triggered_action> ::=
                    ON UPDATE <referential_action> |
                    ON DELETE <referential_action>

                    <referential_action> ::= CASCADE | RESTRICT | NO ACTION | SET NULL

    <resolution> ::= [CLASS] {column_name} OF superclass_name [AS alias]
    <table_options> ::= <table_option> [[,] <table_option> ...]
        <table_option> ::= REUSE_OID |
                           COMMENT [=] 'table_comment_string' |
                           [CHARSET charset_name] [COLLATE collation_name]

• IF NOT EXISTS: If an identically named table already exists, a new table will not be created without an error.
• table_name: specifies the name of the table to be created (maximum: 254 bytes).
• column_name: specifies the name of the column to be created (maximum: 254 bytes).
• column_type: specifies the data type of the column.
• [SHARED value | DEFAULT value]: specifies the initial value of the column.
• <column_constraint>: specifies the constraint of the column. Available constraints are NOT NULL, UNIQUE, PRIMARY KEY and FOREIGN KEY. For details, see Constraint Definition.
• <default_or_shared_or_ai>: only one of DEFAULT, SHARED, AUTO_INCREMENT can be used. When AUTO_INCREMENT is specified, "(seed, increment)" and "AUTO_INCREMENT = initial_value" cannot be defined at the same time.
• table_comment_string: specifies a table's comment
• column_comment_string: specifies a column's comment.
• index_comment_string: specifies an index's comment.

CREATE TABLE olympic2 (
    host_year        INT    NOT NULL PRIMARY KEY,
    host_nation      VARCHAR(40) NOT NULL,
    host_city        VARCHAR(20) NOT NULL,
    opening_date     DATE        NOT NULL,
    closing_date     DATE        NOT NULL,
    mascot           VARCHAR(20),
    slogan           VARCHAR(40),
    introduction     VARCHAR(1500)
);

The below adds a comment of a table with ALTER statement.

ALTER TABLE olympic2 COMMENT = 'this is new comment for olympic2';

The below includes an index comment when you create a table.

CREATE TABLE tbl (a INT, index i_t_a (a) COMMENT 'index comment');

CREATE TABLE tbl (
    id INT PRIMARY KEY,
    CHECK (id > 0)
)

Column Definition¶

A column is a set of data values of a particular simple type, one for each row of the table.

<column_definition> ::=
    column_name <data_type> [[<default_or_shared_or_ai>] | [<column_constraint>]] ... [COMMENT 'comment_string']

    <data_type> ::= <column_type> [<charset_modifier_clause>] [<collation_modifier_clause>]

        <charset_modifier_clause> ::= {CHARACTER_SET|CHARSET} {<char_string_literal>|<identifier>}

        <collation_modifier_clause> ::= COLLATE {<char_string_literal>|<identifier>}

    <default_or_shared_or_ai> ::=
        SHARED <value_specification> |
        DEFAULT <value_specification>  |
        AUTO_INCREMENT [(seed, increment)]

    <column_constraint> ::= [CONSTRAINT constraint_name] {NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY <referential_definition>}

CREATE TABLE manager2 (full_name VARCHAR(40), age INT );

CREATE TABLE colval_tbl
(id INT, name VARCHAR SHARED 'AAA', phone VARCHAR DEFAULT '000-0000');
INSERT INTO colval_tbl (id) VALUES (1), (2);
SELECT * FROM colval_tbl;

           id  name                  phone
=========================================================
            1  'AAA'                 '000-0000'
            2  'AAA'                 '000-0000'

--updating column values on every row
INSERT INTO colval_tbl(id, name) VALUES (3,'BBB');
INSERT INTO colval_tbl(id) VALUES (4),(5);
SELECT * FROM colval_tbl;

           id  name                  phone
=========================================================
            1  'BBB'                 '000-0000'
            2  'BBB'                 '000-0000'
            3  'BBB'                 '000-0000'
            4  'BBB'                 '000-0000'
            5  'BBB'                 '000-0000'

--changing DEFAULT value in the ALTER TABLE statement
ALTER TABLE colval_tbl MODIFY phone VARCHAR DEFAULT '111-1111';
INSERT INTO colval_tbl (id) VALUES (6);
SELECT * FROM colval_tbl;

           id  name                  phone
=========================================================
            1  'BBB'                 '000-0000'
            2  'BBB'                 '000-0000'
            3  'BBB'                 '000-0000'
            4  'BBB'                 '000-0000'
            5  'BBB'                 '000-0000'
            6  'BBB'                 '111-1111'

CREATE TABLE tbl (date1 DATE DEFAULT SYSDATE, date2 DATE DEFAULT SYSDATE);
CREATE TABLE tbl (date1 DATE DEFAULT SYSDATE,
                  ts1   TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE table_name (id INT AUTO_INCREMENT[(seed, increment)]);

CREATE TABLE table_name (id INT AUTO_INCREMENT) AUTO_INCREMENT = seed ;

CREATE TABLE auto_tbl (id INT AUTO_INCREMENT, name VARCHAR);
INSERT INTO auto_tbl VALUES (NULL, 'AAA'), (NULL, 'BBB'), (NULL, 'CCC');
INSERT INTO auto_tbl (name) VALUES ('DDD'), ('EEE');
SELECT * FROM auto_tbl;

           id  name
===================================
            1  'AAA'
            2  'BBB'
            3  'CCC'
            4  'DDD'
            5  'EEE'

CREATE TABLE tbl (id INT AUTO_INCREMENT, val string) AUTO_INCREMENT = 3;
INSERT INTO tbl VALUES (NULL, 'cubrid');

SELECT * FROM tbl;

           id  val
===================================
            3  'cubrid'

CREATE TABLE t (id INT AUTO_INCREMENT, id2 int AUTO_INCREMENT) AUTO_INCREMENT = 5;

ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to  have exactly one AUTO_INCREMENT column and no seed/increment specification.

CREATE TABLE t (i INT AUTO_INCREMENT(100, 2)) AUTO_INCREMENT = 3;

ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to  have exactly one AUTO_INCREMENT column and no seed/increment specification.

Constraint Definition¶

You can define NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY as the constraints. You can also create an index by using INDEX or KEY.

<column_constraint> ::= [CONSTRAINT constraint_name] { NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY <referential_definition> }

<table_constraint> ::=
    [CONSTRAINT [constraint_name]]
    {
        UNIQUE [KEY|INDEX](column_name, ...) |
        {KEY|INDEX} [constraint_name](column_name, ...) |
        PRIMARY KEY (column_name, ...) |
        <referential_constraint>
    }

    <referential_constraint> ::= FOREIGN KEY [<foreign_key_name>](column_name, ...) <referential_definition>

        <referential_definition> ::=
            REFERENCES [referenced_table_name] (column_name, ...) [<referential_triggered_action> ...]

            <referential_triggered_action> ::=
                ON UPDATE <referential_action> |
                ON DELETE <referential_action>

                <referential_action> ::= CASCADE | RESTRICT | NO ACTION | SET NULL

CREATE TABLE const_tbl1(id INT NOT NULL, INDEX i_index(id ASC), phone VARCHAR);

CREATE TABLE const_tbl2(id INT NOT NULL PRIMARY KEY, phone VARCHAR);
INSERT INTO const_tbl2 VALUES (NULL,'000-0000');

Putting value 'null' into attribute 'id' returned: Attribute "id" cannot be made NULL.

-- UNIQUE constraint is defined on a single column only
CREATE TABLE const_tbl5(id INT UNIQUE, phone VARCHAR);
INSERT INTO const_tbl5(id) VALUES (NULL), (NULL);
INSERT INTO const_tbl5 VALUES (1, '000-0000');
SELECT * FROM const_tbl5;

   id  phone
=================
 NULL  NULL
 NULL  NULL
    1  '000-0000'

INSERT INTO const_tbl5 VALUES (1, '111-1111');

ERROR: Operation would have caused one or more unique constraint violations.

-- UNIQUE constraint is defined on several columns
CREATE TABLE const_tbl6(id INT, phone VARCHAR, CONSTRAINT UNIQUE (id, phone));
INSERT INTO const_tbl6 VALUES (1, NULL), (2, NULL), (1, '000-0000'), (1, '111-1111');
SELECT * FROM const_tbl6;

   id  phone
====================
    1  NULL
    2  NULL
    1  '000-0000'
    1  '111-1111'

CREATE TABLE pk_tbl (a INT, b INT, PRIMARY KEY (a, b DESC));

CREATE TABLE const_tbl7 (
    id INT NOT NULL,
    phone VARCHAR,
    CONSTRAINT pk_id PRIMARY KEY (id)
);

-- CONSTRAINT keyword
CREATE TABLE const_tbl8 (
    id INT NOT NULL PRIMARY KEY,
    phone VARCHAR
);

-- primary key is defined on multiple columns
CREATE TABLE const_tbl8 (
    host_year    INT NOT NULL,
    event_code   INT NOT NULL,
    athlete_code INT NOT NULL,
    medal        CHAR (1)  NOT NULL,
    score        VARCHAR (20),
    unit         VARCHAR (5),
    PRIMARY KEY (host_year, event_code, athlete_code, medal)
);

[CONSTRAINT constraint_name] FOREIGN KEY [foreign_key_name] (<column_name_comma_list1>) REFERENCES [referenced_table_name] (<column_name_comma_list2>) [<referential_triggered_action> ...]

    <referential_triggered_action> ::=
        ON UPDATE <referential_action> |
        ON DELETE <referential_action>

        <referential_action> ::= CASCADE | RESTRICT | NO ACTION  | SET NULL

-- creating two tables where one is referring to the other
CREATE TABLE a_tbl (
    id INT NOT NULL DEFAULT 0 PRIMARY KEY,
    phone VARCHAR(10)
);

CREATE TABLE b_tbl (
    ID INT NOT NULL,
    name VARCHAR (10) NOT NULL,
    CONSTRAINT pk_id PRIMARY KEY (id),
    CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES a_tbl (id)
    ON DELETE CASCADE ON UPDATE RESTRICT
);

INSERT INTO a_tbl VALUES (1,'111-1111'), (2,'222-2222'), (3, '333-3333');
INSERT INTO b_tbl VALUES (1,'George'),(2,'Laura'), (3,'Max');
SELECT a.id, b.id, a.phone, b.name FROM a_tbl a, b_tbl b WHERE a.id = b.id;

   id           id                   phone                 name
======================================================================
    1            1                   '111-1111'            'George'
    2            2                   '222-2222'            'Laura'
    3            3                   '333-3333'            'Max'

-- when deleting primary key value, it cascades foreign key value
DELETE FROM a_tbl WHERE id=3;

1 row affected.

SELECT a.id, b.id, a.phone, b.name FROM a_tbl a, b_tbl b WHERE a.id = b.id;

   id           id                   phone                 name
======================================================================
    1            1                   '111-1111'            'George'
    2            2                   '222-2222'            'Laura'

-- when attempting to update primary key value, it restricts the operation
UPDATE  a_tbl SET id = 10 WHERE phone = '111-1111';

ERROR: Update/Delete operations are restricted by the foreign key 'fk_id'.

CREATE TABLE const_tbl4(id INT, phone VARCHAR, KEY i_key(id DESC, phone ASC));

Column Option¶

You can specify options such as ASC or DESC after the column name when defining UNIQUE or INDEX for a specific column. This keyword is specified as store the index value in ascending or descending order.

column_name [ASC | DESC]

CREATE TABLE const_tbl(
    id VARCHAR,
    name VARCHAR,
    CONSTRAINT UNIQUE INDEX(id DESC, name ASC)
);

INSERT INTO const_tbl VALUES('1000', 'john'), ('1000','johnny'), ('1000', 'jone');
INSERT INTO const_tbl VALUES('1001', 'johnny'), ('1001','john'), ('1001', 'jone');

SELECT * FROM const_tbl WHERE id > '100';

  id    name
=================
  1001     john
  1001     johnny
  1001     jone
  1000     john
  1000     johnny
  1000     jone

Table Option¶

-- creating table with REUSE_OID option specified
CREATE TABLE reuse_tbl (a INT PRIMARY KEY) REUSE_OID, COMMENT = 'reuse oid table';
INSERT INTO reuse_tbl VALUES (1);
INSERT INTO reuse_tbl VALUES (2);
INSERT INTO reuse_tbl VALUES (3);

-- an error occurs when column type is a OID reusable table itself
CREATE TABLE tbl_1 (a reuse_tbl);

ERROR: The class 'reuse_tbl' is marked as REUSE_OID and is non-referable. Non-referable classes can't be the domain of an attribute and their instances' OIDs cannot be returned.

CREATE TABLE t3(a VARCHAR(20)) REUSE_OID, COMMENT = 'reuse oid table', COLLATE euckr_bin;
CREATE TABLE t4(a VARCHAR(20)) COLLATE euckr_bin REUSE_OID;

CREATE TABLE tbl (a INT, b INT) COMMENT = 'this is comment for table tbl';

SHOW CREATE TABLE table_name;
SELECT class_name, comment from db_class;
SELECT class_name, comment from _db_class;

$ csql -u dba demodb

csql> ;sc tbl

CREATE TABLE LIKE¶

You can create a table that has the same schema as an existing table by using the CREATE TABLE ... LIKE statement. Column attribute, table constraint, and index are replicated from the existing table. An index name created from the existing table changes according to a new table name, but an index name defined by a user is replicated as it is. Therefore, you should be careful at a query statement that is supposed to use a specific index created by using the index hint syntax(see Index Hint).

You cannot create the column definition because the CREATE TABLE ... LIKE statement replicates the schema only.

CREATE {TABLE | CLASS} <new_table_name> LIKE <source_table_name>;

• new_table_name: A table name to be created

• source_table_name: The name of the original table that already exists in the database. The following tables cannot be specified as original tables in the CREATE TABLE ... LIKE statement.

  • Partition table
  • Table that contains an AUTO_INCREMENT column
  • Table that uses inheritance or methods

CREATE TABLE a_tbl (
  id INT NOT NULL DEFAULT 0 PRIMARY KEY,
  phone VARCHAR(10)
);
INSERT INTO a_tbl VALUES (1,'111-1111'), (2,'222-2222'), (3, '333-3333');

-- creating an empty table with the same schema as a_tbl
CREATE TABLE new_tbl LIKE a_tbl;
SELECT * FROM new_tbl;

There are no results.

csql> ;schema a_tbl

=== <Help: Schema of a Class> ===


 <Class Name>

     a_tbl

 <Attributes>

     id                   INTEGER DEFAULT 0 NOT NULL
     phone                CHARACTER VARYING(10)

 <Constraints>

     PRIMARY KEY pk_a_tbl_id ON a_tbl (id)

csql> ;schema new_tbl

=== <Help: Schema of a Class> ===


 <Class Name>

     new_tbl

 <Attributes>

     id                   INTEGER DEFAULT 0 NOT NULL
     phone                CHARACTER VARYING(10)

 <Constraints>

     PRIMARY KEY pk_new_tbl_id ON new_tbl (id)

CREATE TABLE AS SELECT¶

You can create a new table that contains the result records of the SELECT statement by using the CREATE TABLE...AS SELECT statement. You can define column and table constraints for the new table. The following rules are applied to reflect the result records of the SELECT statement.

• If col_1 is defined in the new table and the same column col_1 is specified in select_statement, the result record of the SELECT statement is stored as col_1 value in the new table. Type casting is attempted if the column names are identical but the columns types are different.
• If col_1 and col_2 are defined in the new table, col_1, col_2 and col_3 are specified in the column list of the select_statement and there is a containment relationship between all of them, col_1, col_2 and col_3 are created in the new table and the result data of the SELECT statement is stored as values for all columns. Type casting is attempted if the column names are identical but the columns types are different.
• If columns col_1 and col_2 are defined in the new table and col_1 and col_3 are defined in the column list of select_statement without any containment relationship between them, col_1, col_2 and col_3 are created in the new table, the result data of the SELECT statement is stored only for col_1 and col_3 which are specified in select_statement, and NULL is stored as the value of col_2.
• Column aliases can be included in the column list of select_statement. In this case, new column alias is used as a new table column name. It is recommended to use an alias because invalid column name is created, if an alias does not exist when a function calling or an expression is used.
• The REPLACE option is valid only when the UNIQUE constraint is defined in a new table column (col_1). When duplicate values exist in the result record of select_statement, a UNIQUE value is stored for col_1 if the REPLACE option has been defined, or an error message is displayed if the REPLACE option is omitted due to the violation of the UNIQUE constraint.

CREATE {TABLE | CLASS} table_name [(<column_definition> [,<table_constraint>], ...)] [REPLACE] AS <select_statement>;

• table_name: a name of the table to be created.
• <column_definition>: defines a column. If this is omitted, the column schema of SELECT statement is replicated; however, the constraint or the AUTO_INCREMENT attribute is not replicated.
• <table_constraint>: defines table constraint.
• <select_statement>: a SELECT statement targeting a source table that already exists in the database.

CREATE TABLE a_tbl (
  id INT NOT NULL DEFAULT 0 PRIMARY KEY,
  phone VARCHAR(10)
);
INSERT INTO a_tbl VALUES (1,'111-1111'), (2,'222-2222'), (3, '333-3333');

-- creating a table without column definition
CREATE TABLE new_tbl1 AS SELECT * FROM a_tbl;
SELECT * FROM new_tbl1;

   id  phone
===================================
    1  '111-1111'
    2  '222-2222'
    3  '333-3333'

-- all of column values are replicated from a_tbl
CREATE TABLE new_tbl2 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR
) AS SELECT * FROM a_tbl;

SELECT * FROM new_tbl2;

   id  phone
===================================
    1  '111-1111'
    2  '222-2222'
    3  '333-3333'

-- some of column values are replicated from a_tbl and the rest is NULL
CREATE TABLE new_tbl3 (
  id INT,
  name VARCHAR
) AS SELECT id, phone FROM a_tbl;

SELECT * FROM new_tbl3

  name                           id  phone
=========================================================
  NULL                            1  '111-1111'
  NULL                            2  '222-2222'
  NULL                            3  '333-3333'

-- column alias in the select statement should be used in the column definition
CREATE TABLE new_tbl4 (
  id1 INT,
  id2 INT
) AS SELECT t1.id id1, t2.id id2 FROM new_tbl1 t1, new_tbl2 t2;

SELECT * FROM new_tbl4;

  id1          id2
==========================
    1            1
    1            2
    1            3
    2            1
    2            2
    2            3
    3            1
    3            2
    3            3

-- REPLACE is used on the UNIQUE column
CREATE TABLE new_tbl5 (id1 int UNIQUE) REPLACE AS SELECT * FROM new_tbl4;

SELECT * FROM new_tbl5;

  id1          id2
==========================
    1            3
    2            3
    3            3

ADD COLUMN Clause¶

You can add a new column by using the ADD COLUMN clause. You can specify the location of the column to be added by using the FIRST or AFTER keyword.

ALTER [TABLE | CLASS] table_name
ADD [COLUMN | ATTRIBUTE] [(] <column_definition> [FIRST | AFTER old_column_name] [)];

    <column_definition> ::=
        column_name <data_type> [[<default_or_shared_or_ai>] | [<column_constraint>]] [COMMENT 'comment_string']

        <data_type> ::= <column_type> [<charset_modifier_clause>] [<collation_modifier_clause>]

            <charset_modifier_clause> ::= {CHARACTER_SET|CHARSET} {<char_string_literal>|<identifier>}

            <collation_modifier_clause> ::= COLLATE {<char_string_literal>|<identifier>}

        <default_or_shared_or_ai> ::=
            SHARED <value_specification> |
            DEFAULT <value_specification>  |
            AUTO_INCREMENT [(seed, increment)]

        <column_constraint> ::= [CONSTRAINT constraint_name] {NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY <referential_definition>}

            <referential_definition> ::=
                REFERENCES [referenced_table_name] (column_name, ...) [<referential_triggered_action> ...]

                <referential_triggered_action> ::=
                    ON UPDATE <referential_action> |
                    ON DELETE <referential_action>

                    <referential_action> ::= CASCADE | RESTRICT | NO ACTION | SET NULL

• table_name: specifies the name of a table that has a column to be added.
• <column_definition>: specifies the name(max 254 bytes), data type, and constraints of a column to be added.
• AFTER oid_column_name: specifies the name of an existing column before the column to be added.
• comment_string: specifies a column's comment.

CREATE TABLE a_tbl;
ALTER TABLE a_tbl ADD COLUMN age INT DEFAULT 0 NOT NULL COMMENT 'age comment';
ALTER TABLE a_tbl ADD COLUMN name VARCHAR FIRST;
ALTER TABLE a_tbl ADD COLUMN id INT NOT NULL AUTO_INCREMENT UNIQUE FIRST;
INSERT INTO a_tbl(age) VALUES(20),(30),(40);

ALTER TABLE a_tbl ADD COLUMN phone VARCHAR(13) DEFAULT '000-0000-0000' AFTER name;

SELECT * FROM a_tbl;

   id  name                  phone                         age
==============================================================
    1  NULL                  '000-0000-0000'                20
    2  NULL                  '000-0000-0000'                30
    3  NULL                  '000-0000-0000'                40

--adding multiple columns
ALTER TABLE a_tbl ADD COLUMN (age1 int, age2 int, age3 int);

The result when you add a new column depends on what constraints are added.

• If there is a DEFAULT constraint on the newly added column, DEFAULT value is inserted.
• If there is no DEFAULT constraint and there is a NOT NULL constraint, hard default value is inserted when a value of system parameter add_column_update_hard_default is yes; however, it returns an error when a value of add_column_update_hard_default is no.

The default of add_column_update_hard_default is no.

Depending on DEFAULT constraint and add_column_update_hard_default's value, if they do not violate their constraints, it is possible to add PRIMARY KEY constraint or UNIQUE constraint.

• If the newly added column when there is no data on the table, or the newly added column with NOT NULL and UNIQUE data can have PRIMARY KEY constraint.

• If you try to add a new column with PRIMARY KEY constraint when there is data on the table, it returns an error.

  CREATE TABLE tbl (a INT);
  INSERT INTO tbl VALUES (1), (2);
  ALTER TABLE tbl ADD COLUMN (b int PRIMARY KEY);
  

  ERROR: NOT NULL constraints do not allow NULL value.
  

• If there is data and UNIQUE constraint is specified on the newly added data, NULL is inserted when there is no DEFAULT constraint.

  ALTER TABLE tbl ADD COLUMN (b int UNIQUE);
  SELECT * FROM tbl;
  

      a            b
  ==================
      1         NULL
      2         NULL
  

• If there is data on the table and UNIQUE constraint is specified on the newly added column, unique violation error is returned when there is DEFAULT constraint.

  ALTER TABLE tbl ADD COLUMN (c int UNIQUE DEFAULT 10);
  

  ERROR: Operation would have caused one or more unique constraint violations.
  

• If there is data on the table and UNIQUE constraint is specified on the newly added column, unique violation error is returned when there is NOT NULL constraint and the value of system parameter add_column_update_hard_default is yes.

  SET SYSTEM PARAMETERS 'add_column_update_hard_default=yes';
  ALTER TABLE tbl ADD COLUMN (c int UNIQUE NOT NULL);
  

  ERROR: Operation would have caused one or more unique constraint violations.
  

For add_column_update_hard_default and the hard default, see CHANGE/MODIFY Clauses.

ADD CONSTRAINT Clause¶

You can add a new constraint by using the ADD CONSTRAINT clause.

By default, the index created when you add PRIMARY KEY constraints is created in ascending order, and you can define the key sorting order by specifying the ASC or DESC keyword next to the column name.

ALTER [ TABLE | CLASS | VCLASS | VIEW ] table_name
ADD <table_constraint> ;

    <table_constraint> ::=
        [CONSTRAINT [constraint_name]]
        {
            UNIQUE [KEY|INDEX](column_name, ...) |
            {KEY|INDEX} [constraint_name](column_name, ...) |
            PRIMARY KEY (column_name, ...) |
            <referential_constraint>
        }

        <referential_constraint> ::= FOREIGN KEY [foreign_key_name](column_name, ...) <referential_definition>

            <referential_definition> ::=
                REFERENCES [referenced_table_name] (column_name, ...) [<referential_triggered_action> ...]

                <referential_triggered_action> ::=
                    ON UPDATE <referential_action> |
                    ON DELETE <referential_action>

                    <referential_action> ::= CASCADE | RESTRICT | NO ACTION | SET NULL

• table_name: Specifies the name of a table that has a constraint to be added.
• constraint_name: Specifies the name of a constraint to be added, or it can be omitted. If omitted, a name is automatically assigned(maximum: 254 bytes).
• foreign_key_name: Specifies a name of the FOREIGN KEY constraint. You can skip the name specification. However, if you specify this value, constraint_name will be ignored, and the specified value will be used.
• <table_constraint>: Defines a constraint for the specified table. For details, see Constraint Definition.

ALTER TABLE a_tbl ADD CONSTRAINT pk_a_tbl_id PRIMARY KEY(id);
ALTER TABLE a_tbl DROP CONSTRAINT pk_a_tbl_id;
ALTER TABLE a_tbl ADD CONSTRAINT pk_a_tbl_id PRIMARY KEY(id, name DESC);
ALTER TABLE a_tbl ADD CONSTRAINT u_key1 UNIQUE (id);

ADD INDEX Clause¶

You can define the index attributes for a specific column by using the ADD INDEX clause.

ALTER [TABLE | CLASS] table_name ADD {KEY | INDEX} index_name (<index_col_name>) ;

    <index_col_name> ::= column_name [(length)] [ ASC | DESC ]

• table_name : Specifies the name of a table to be modified.
• index_name : Specifies the name of an index(maximum: 254 bytes). If omitted, a name is automatically assigned.
• index_col_name : Specifies the column that has an index to be defined. ASC or DESC can be specified for a column option.

ALTER TABLE a_tbl ADD INDEX i1(age ASC), ADD INDEX i2(phone DESC);

csql> ;schema a_tbl

=== <Help: Schema of a Class> ===

 <Class Name>

     a_tbl

<Attributes>

     name                 CHARACTER VARYING(1073741823) DEFAULT ''
     phone                CHARACTER VARYING(13) DEFAULT '111-1111'
     age                  INTEGER
     id                   INTEGER AUTO_INCREMENT  NOT NULL

 <Constraints>

     UNIQUE u_a_tbl_id ON a_tbl (id)
     INDEX i1 ON a_tbl (age)
     INDEX i2 ON a_tbl (phone DESC)

The below is an example to include an index's comment when you add an index with ALTER statement.

ALTER TABLE tbl ADD index i_t_c (c) COMMENT 'index comment c';

ALTER COLUMN ... SET DEFAULT Clause¶

You can specify a new default value for a column that has no default value or modify the existing default value by using the ALTER COLUMN ... SET DEFAULT. You can use the CHANGE clause to change the default value of multiple columns with a single statement. For details, see the CHANGE/MODIFY Clauses.

ALTER [TABLE | CLASS] table_name ALTER [COLUMN] column_name SET DEFAULT value ;

• table_name : Specifies the name of a table that has a column whose default value is to be modified.
• column_name : Specifies the name of a column whose default value is to be modified.
• value : Specifies a new default value.

csql> ;schema a_tbl

=== <Help: Schema of a Class> ===


 <Class Name>

     a_tbl

 <Attributes>

     name                 CHARACTER VARYING(1073741823)
     phone                CHARACTER VARYING(13) DEFAULT '000-0000-0000'
     age                  INTEGER
     id                   INTEGER AUTO_INCREMENT  NOT NULL

 <Constraints>

     UNIQUE u_a_tbl_id ON a_tbl (id)

ALTER TABLE a_tbl ALTER COLUMN name SET DEFAULT '';
ALTER TABLE a_tbl ALTER COLUMN phone SET DEFAULT '111-1111';

csql> ;schema a_tbl

=== <Help: Schema of a Class> ===


 <Class Name>

     a_tbl

 <Attributes>

     name                 CHARACTER VARYING(1073741823) DEFAULT ''
     phone                CHARACTER VARYING(13) DEFAULT '111-1111'
     age                  INTEGER
     id                   INTEGER AUTO_INCREMENT  NOT NULL

 <Constraints>

     UNIQUE u_a_tbl_id ON a_tbl (id)

AUTO_INCREMENT Clause¶

The AUTO_INCREMENT clause can change the initial value of the increment value that is currently defined. However, there should be only one AUTO_INCREMENT column defined.

ALTER TABLE table_name AUTO_INCREMENT = initial_value ;

• table_name : Table name
• initial_value : Initial value to alter

CREATE TABLE t (i int AUTO_INCREMENT);
ALTER TABLE t AUTO_INCREMENT = 5;

CREATE TABLE t (i int AUTO_INCREMENT, j int AUTO_INCREMENT);

-- when 2 AUTO_INCREMENT constraints are defined on one table, below query returns an error.
ALTER TABLE t AUTO_INCREMENT = 5;

ERROR: To avoid ambiguity, the AUTO_INCREMENT table option requires the table to have exactly one AUTO_INCREMENT column and no seed/increment specification.

CHANGE/MODIFY Clauses¶

The CHANGE clause changes column name, type, size, and attribute. If the existing column name and a new column name are the same, types, size, and attribute will be changed.

The MODIFY clause can modify type, size, and attribute of a column but cannot change its name.

If you set the type, size, and attribute to apply to a new column with the CHANGE clause or the MODIFY clause, the attribute that is currently defined will not be passed to the attribute of the new column.

When you change data types using the CHANGE clause or the MODIFY clause, the data can be modified. For example, if you shorten the length of a column, the character string may be truncated.

ALTER [/*+ SKIP_UPDATE_NULL */] TABLE tbl_name <table_options> ;

    <table_options> ::=
        <table_option>[, <table_option>, ...]

        <table_option> ::=
            CHANGE [COLUMN | CLASS ATTRIBUTE] old_col_name new_col_name <column_definition>
                     [FIRST | AFTER col_name]
          | MODIFY [COLUMN | CLASS ATTRIBUTE] col_name <column_definition>
                     [FIRST | AFTER col_name]

• tbl_name: specifies the name of the table including the column to change.
• old_col_name: specifies the existing column name.
• new_col_name: specifies the column name to change
• <column_definition>: specifies the type, the size, the attribute, and the comment of the column to be changed.
• col_name: specifies the location where the column to change exists.
• SKIP_UPDATE_NULL: If this hint is added, CUBRID does not check the previous NULLs even if NOT NULL constraint is added. See SKIP_UPDATE_NULL.

CREATE TABLE t1 (a INTEGER);

-- changing column a's name into a1
ALTER TABLE t1 CHANGE a a1 INTEGER;

-- changing column a1's constraint
ALTER TABLE t1 CHANGE a1 a1 INTEGER NOT NULL;
---- or
ALTER TABLE t1 MODIFY a1 INTEGER NOT NULL;

-- changing column col1's type - "DEFAULT 1" constraint is removed.
CREATE TABLE t1 (col1 INT DEFAULT 1);
ALTER TABLE t1 MODIFY col1 BIGINT;

-- changing column col1's type - "DEFAULT 1" constraint is kept.
CREATE TABLE t1 (col1 INT DEFAULT 1, b VARCHAR(10));
ALTER TABLE t1 MODIFY col1 BIGINT DEFAULT 1;

-- changing column b's size
ALTER TABLE t1 MODIFY b VARCHAR(20);

-- changing the name and position of a column
CREATE TABLE t1 (i1 INT, i2 INT);
INSERT INTO t1 VALUES (1,11), (2,22), (3,33);

SELECT * FROM t1 ORDER BY 1;

            i1           i2
==========================
             1           11
             2           22
             3           33

ALTER TABLE t1 CHANGE i2 i0 INTEGER FIRST;
SELECT * FROM t1 ORDER BY 1;

            i0           i1
==========================
            11            1
            22            2
            33            3

-- adding NOT NULL constraint (strict)
SET SYSTEM PARAMETERS 'alter_table_change_type_strict=yes';

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (11), (NULL), (22);

ALTER TABLE t1 CHANGE i i1 INTEGER NOT NULL;

ERROR: Cannot add NOT NULL constraint for attribute "i1": there are existing NULL values for this attribute.

-- adding NOT NULL constraint
SET SYSTEM PARAMETERS 'alter_table_change_type_strict=no';

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (11), (NULL), (22);

ALTER TABLE t1 CHANGE i i1 INTEGER NOT NULL;

SELECT * FROM t1;

           i1
=============
           22
            0
           11

-- change the column's data type (no errors)

CREATE TABLE t1 (i1 INT);
INSERT INTO t1 VALUES (1), (-2147483648), (2147483647);

ALTER TABLE t1 CHANGE i1 s1 CHAR(11);
SELECT * FROM t1;

  s1
======================
  '2147483647 '
  '-2147483648'
  '1          '

-- change the column's data type (errors), strict mode
SET SYSTEM PARAMETERS 'alter_table_change_type_strict=yes';

CREATE TABLE t1 (i1 INT);
INSERT INTO t1 VALUES (1), (-2147483648), (2147483647);

ALTER TABLE t1 CHANGE i1 s1 CHAR(4);

ERROR: ALTER TABLE .. CHANGE : changing to new domain : cast failed, current configuration doesn't allow truncation or overflow.

-- change the column's data type (errors)
SET SYSTEM PARAMETERS 'alter_table_change_type_strict=no';

CREATE TABLE t1 (i1 INT);
INSERT INTO t1 VALUES (1), (-2147483648), (2147483647);

ALTER TABLE t1 CHANGE i1 s1 CHAR(4);
SELECT * FROM t1;

-- hard default values have been placed instead of signaling overflow

  s1
======================
  '1   '
  '-214'
  '2147'

ALTER /*+ SKIP_UPDATE_NULL */ TABLE foo MODIFY col INT NOT NULL;

Column's COMMENT¶

A column's comment is specified in <column_definition>, which is located at the end of ADD/MODIFY/CHANGE syntax. To see the meaning of <column_definition>, refer to CREATE TABLE syntax on the above.

The below is a syntax to show a column's comment.

SHOW CREATE TABLE table_name;

SELECT attr_name, class_name, comment
FROM db_attribute WHERE class_name ='classname';

SHOW FULL COLUMNS FROM table_name;

You can see this comment with the ";sc table_name" command in the CSQL interpreter.

$ csql -u dba demodb

csql> ;sc table_name

RENAME COLUMN Clause¶

You can change the name of the column by using the RENAME COLUMN clause.

ALTER [ TABLE | CLASS | VCLASS | VIEW ] table_name
RENAME [ COLUMN | ATTRIBUTE ] old_column_name { AS | TO } new_column_name ;

• table_name : Specifies the name of a table that has a column to be renamed.
• old_column_name : Specifies the name of a column.
• new_column_name : Specifies a new column name after the AS keyword(maximum: 254 bytes).

CREATE TABLE a_tbl (id INT, name VARCHAR(50));
ALTER TABLE a_tbl RENAME COLUMN name AS name1;

DROP COLUMN Clause¶

You can delete a column in a table by using the DROP COLUMN clause. You can specify multiple columns to delete simultaneously by separating them with commas (,).

ALTER [TABLE | CLASS | VCLASS | VIEW] table_name
DROP [COLUMN | ATTRIBUTE] column_name, ... ;

• table_name : Specifies the name of a table that has a column to be deleted.
• column_ name : Specifies the name of a column to be deleted. Multiple columns can be specified by separating them with commas (,).

ALTER TABLE a_tbl DROP COLUMN age1,age2,age3;

DROP CONSTRAINT Clause¶

You can drop the constraints pre-defined for the table, such as UNIQUE, PRIMARY KEY and FOREIGN KEY by using the DROP CONSTRAINT clause. In this case, you must specify a constraint name. You can check these names by using the CSQL command (;schema table_name).

ALTER [TABLE | CLASS] table_name
DROP CONSTRAINT constraint_name ;

• table_name : Specifies the name of a table that has a constraint to be dropped.
• constraint_name : Specifies the name of a constraint to be dropped.

CREATE TABLE a_tbl (
  id INT NOT NULL DEFAULT 0 PRIMARY KEY,
  phone VARCHAR(10)
);

CREATE TABLE b_tbl (
  ID INT NOT NULL,
  name VARCHAR (10) NOT NULL,
  CONSTRAINT u_name UNIQUE (name),
  CONSTRAINT pk_id PRIMARY KEY (id),
  CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES a_tbl (id)
  ON DELETE CASCADE ON UPDATE RESTRICT
);

ALTER TABLE b_tbl DROP CONSTRAINT pk_id;
ALTER TABLE b_tbl DROP CONSTRAINT fk_id;
ALTER TABLE b_tbl DROP CONSTRAINT u_name;

DROP INDEX Clause¶

You can delete an index defined for a column by using the DROP INDEX clause. A unique index can be dropped with a DROP CONSTRAINT clause.

ALTER [TABLE | CLASS] table_name DROP INDEX index_name ;

• table_name : Specifies the name of a table of which constraints will be deleted.
• index_name : Specifies the name of an index to be deleted.

ALTER TABLE a_tbl DROP INDEX i_a_tbl_age;

DROP PRIMARY KEY Clause¶

You can delete a primary key constraint defined for a table by using the DROP PRIMARY KEY clause. You do have to specify the name of the primary key constraint because only one primary key can be defined by table.

ALTER [TABLE | CLASS] table_name DROP PRIMARY KEY ;

• table_name : Specifies the name of a table that has a primary key constraint to be deleted.

ALTER TABLE a_tbl DROP PRIMARY KEY;

DROP FOREIGN KEY Clause¶

You can drop a foreign key constraint defined for a table using the DROP FOREIGN KEY clause.

ALTER [TABLE | CLASS] table_name DROP FOREIGN KEY constraint_name ;

• table_name : Specifies the name of a table whose constraint is to be deleted.
• constraint_name : Specifies the name of foreign key constraint to be deleted.

ALTER TABLE b_tbl ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES a_tbl (id);
ALTER TABLE b_tbl DROP FOREIGN KEY fk_id;